package cn.com.wangluotongxin.database.command.impl;

import cn.com.wangluotongxin.admin.model.AdminMerchantsDatabaseSync;
import cn.com.wangluotongxin.admin.model.constants.DatabaseReleaseStatus;
import cn.com.wangluotongxin.admin.model.constants.DatabaseRunStatus;
import cn.com.wangluotongxin.database.command.DatabaseCommand;
import cn.com.wangluotongxin.database.mysql.LoadMysqlDatabaseInfoService;
import cn.com.wangluotongxin.database.mysql.utils.SourceMySqlDatabaseUtil;
import cn.com.wangluotongxin.database.mysql.utils.SourceMySqlTableColumnsUtil;
import cn.com.wangluotongxin.database.mysql.utils.SourceMySqlTableIndexesUtil;
import cn.com.wangluotongxin.database.mysql.utils.SourceMySqlTablesUtil;
import cn.com.wangluotongxin.database.service.IAdminMerchantsDatabaseService;
import cn.com.wangluotongxin.database.service.IAdminMerchantsDatabaseSyncService;
import cn.com.wangluotongxin.mysql.DatabaseConnection;
import cn.com.wangluotongxin.mysql.pojo.DatabaseVo;
import cn.com.wangluotongxin.mysql.pojo.TableColumnsVo;
import cn.com.wangluotongxin.mysql.pojo.TableIndexVo;
import cn.com.wangluotongxin.mysql.pojo.TableVo;
import cn.com.wangluotongxin.mysql.service.DatabaseFrameService;
import cn.com.wangluotongxin.mysql.service.SqlConnection;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Slf4j
@Component
public class MySqlCommandImpl implements DatabaseCommand {

    @Autowired
    private IAdminMerchantsDatabaseSyncService merchantsDatabaseSyncService;

    @Autowired
    private IAdminMerchantsDatabaseService merchantsDatabaseService;

    @Autowired
    private LoadMysqlDatabaseInfoService databaseInfoService;

    @Override
    public void releaseDatabase(Integer databaseId, String domain, Integer port, String databaseName, String username, String password, Integer createUser, String createName, String channelId) {
        try {
            // 创建目标MYSQL链接
            SqlConnection sqlConnection = this.createSqlConnectionNoDatabase(databaseId, domain, port, databaseName, username, password, createUser, createName, channelId);
            if (sqlConnection == null) return;

            // 创建数据库
            sqlConnection = createDatabase(sqlConnection, databaseId, databaseName, createUser, createName, channelId);
            if (sqlConnection == null || sqlConnection.getConnection().isClosed()) return;
            // 创建表
            Boolean tableResult = createTables(sqlConnection, databaseId, databaseName, createUser, createName, channelId);
            if (!tableResult) return;
            // 同步表索引
            this.createTableIndex(sqlConnection, databaseId, databaseName, createUser, createName, channelId);

            // 更新数据库同步状态
            merchantsDatabaseService.updateStatus(databaseId, DatabaseReleaseStatus.SUCCESS, "同步成功", createUser);

            // 事务提交
            sqlConnection.commit(sqlConnection);
        }catch (Exception ex){
            // 更新数据库同步状态
            merchantsDatabaseService.updateStatus(databaseId, DatabaseReleaseStatus.FAIL, ex.getMessage(), createUser);
        }

        // 睡眠2秒 防止数据库结构未重新被加载
        ThreadUtil.sleep(2000);

        // 重新加载表结构
        databaseInfoService.load();
    }

    private void createTableIndex(SqlConnection sqlConnection, Integer databaseId, String databaseName, Integer createUser, String createName, String channelId) {
        List<TableVo> tableVos = SourceMySqlTablesUtil.getTableVos();
        if(ArrayUtil.isAllEmpty(tableVos)) return;

        for(TableVo tableVo : tableVos) {
            AdminMerchantsDatabaseSync merchantsDatabaseSync = null;
            try {
                merchantsDatabaseSync = merchantsDatabaseSyncService.createRecord(databaseId, "发布数据库【" + databaseName + "】", "同步数据库表【" + tableVo.getTableName() + "】索引中", createUser, createName, channelId);

                // 来源表索引
                List<TableIndexVo> sourceIndexVos = SourceMySqlTableIndexesUtil.get(tableVo.getTableName());
                if (ArrayUtil.isAllEmpty(sourceIndexVos)) continue;

                // 目标表索引
                List<TableIndexVo> targetIndexVos = sqlConnection.getIndex(tableVo.getTableName());

                // 新增索引
                List<TableIndexVo> addIndexVos = new ArrayList<>();
                if(ArrayUtil.isAllEmpty(targetIndexVos)) {
                    addIndexVos = sourceIndexVos;
                }else {
                    for(TableIndexVo sourceIndexVo : sourceIndexVos) {
                        TableIndexVo tableIndexVo = targetIndexVos.stream().filter(item-> sourceIndexVo.getIndexName().equals(item.getIndexName())).findFirst().orElse(null);
                        if(tableIndexVo == null) {
                            addIndexVos.add(sourceIndexVo);
                        }
                    }
                }
                if(ArrayUtil.isAllNotEmpty(addIndexVos)) {
                    for(TableIndexVo addIndexVo : addIndexVos) {
                        sqlConnection.addIndex(tableVo.getTableName(), addIndexVo);
                    }
                }

                // 移除索引
                List<TableIndexVo> delTableIndexVos = new ArrayList<>();
                if(ArrayUtil.isAllEmpty(sourceIndexVos)) {
                    delTableIndexVos = targetIndexVos;
                }else {
                    for(TableIndexVo targetIndexVo : targetIndexVos) {
                        TableIndexVo sourceIndexVoIndexVo = sourceIndexVos.stream().filter(item-> targetIndexVo.getIndexName().equals(item.getIndexName())).findFirst().orElse(null);
                        if(sourceIndexVoIndexVo == null) {
                            delTableIndexVos.add(targetIndexVo);
                        }
                    }
                }
                if(ArrayUtil.isAllNotEmpty(delTableIndexVos)) {
                    for(TableIndexVo delIndexVo : delTableIndexVos) {
                        sqlConnection.dropIndex(tableVo.getTableName(), delIndexVo);
                    }
                }
            }catch (Exception ex) {
                if(merchantsDatabaseSync != null) {
                    merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.FAIL, "同步数据库表【" + tableVo.getTableName() + "】索引异常", ex.getMessage(), channelId);
                }
            }
        }
    }

    // 创建表
    public Boolean createTables(SqlConnection connection, Integer databaseId, String databaseName, Integer createUser, String createName, String channelId) {
        List<TableVo> tableVos = SourceMySqlTablesUtil.getTableVos();
        DatabaseVo databaseVo = SourceMySqlDatabaseUtil.getDatabase();
        for(TableVo tableVo : tableVos) {
            // 表已经存在时同步表结构
            TableVo currentCreateTable = DatabaseFrameService.create().setSqlConnection(connection).getTable(tableVo.getTableName());
            if(currentCreateTable != null) {
                this.asyncTableStructure(connection, databaseId, databaseName, tableVo.getTableName(), createUser, createName, channelId);
                continue;
            }
            AdminMerchantsDatabaseSync merchantsDatabaseSync = merchantsDatabaseSyncService.createRecord(databaseId, "发布数据库【"+ databaseName +"】", "创建数据库表【"+ tableVo.getTableName() +"】中", createUser, createName, channelId);
            try {
                connection.createTable(tableVo, tableVo.getTableColumnsVos(), databaseVo.getDefaultCharacterSetName());
                merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.SUCCESS, "创建数据库表【"+ tableVo.getTableName() + "】成功", null, channelId);
            }catch (Exception ex) {
                ex.printStackTrace();
                merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.FAIL, "创建数据库表【"+ tableVo.getTableName() +"】异常", ex.getMessage(), channelId);
                if(connection != null) {
                    connection.close();
                }
                return false;
            }
        }
        return true;
    }

    // 同步表结构
    private void asyncTableStructure(SqlConnection connection, Integer databaseId, String databaseName, String tableName, Integer createUser, String createName, String channelId) {
        AdminMerchantsDatabaseSync merchantsDatabaseSync = merchantsDatabaseSyncService.createRecord(databaseId, "发布数据库【"+ databaseName +"】", "同步数据库表【"+ tableName +"】结构中", createUser, createName, channelId);

        // 当前表列信息
        List<TableColumnsVo> currentColumns = DatabaseFrameService.create().setSqlConnection(connection).getTableColumns(tableName);

        // 来源表列信息
        List<TableColumnsVo> sourceColumns = SourceMySqlTableColumnsUtil.get(tableName);

        for(int i = 0; i < sourceColumns.size(); i++) {
            TableColumnsVo sourceColumn = sourceColumns.get(i);
            // 排除主键
            if(StrUtil.isNotBlank(sourceColumn.getColumnKey()) && sourceColumn.getColumnKey().toUpperCase().equals("PRI")) continue;
            TableColumnsVo currentColumn = currentColumns.stream().filter(item-> sourceColumn.getColumnName().equals(item.getColumnName())).findFirst().orElse(null);

            // 新增列
            if(currentColumn == null) {
                connection.addColumn(tableName, sourceColumn, i > 0? sourceColumns.get(i - 1).getColumnName() : "");

            // 更新列
            }else {
                connection.changeColumn(tableName, sourceColumn);
            }
        }

        // 删除列
        for(int i = 0; i < currentColumns.size(); i++) {
            TableColumnsVo currentColumn = currentColumns.get(i);
            // 排除主键
            if(StrUtil.isNotBlank(currentColumn.getColumnKey()) && currentColumn.getColumnKey().toUpperCase().equals("PRI")) continue;
            TableColumnsVo sourceColumn = sourceColumns.stream().filter(item-> currentColumn.getColumnName().equals(item.getColumnName())).findFirst().orElse(null);
            if(sourceColumn != null) continue;
            connection.delColumn(tableName, currentColumn.getColumnName());
        }

        merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.SUCCESS, "创建数据库表【"+ tableName + "】成功", null, channelId);
    }

    // 创建数据库
    public SqlConnection createDatabase(SqlConnection connection, Integer databaseId, String databaseName, Integer createUser, String createName, String channelId) {
        // 查询数据库
        DatabaseVo currentCreateBase = DatabaseFrameService.create().setSqlConnection(connection).getDatabaseInfo(databaseName);
        if(currentCreateBase != null) {
            connection = connection.setDatabaseName(databaseName).restart();
            return connection;
        }

        AdminMerchantsDatabaseSync merchantsDatabaseSync = merchantsDatabaseSyncService.createRecord(databaseId, "发布数据库【"+ databaseName +"】", "创建数据库中", createUser, createName, channelId);
        try{
            DatabaseVo databaseVo = SourceMySqlDatabaseUtil.getDatabase();
            connection.createDatabase(databaseName, databaseVo.getDefaultCharacterSetName(), databaseVo.getDefaultCollationName());
            merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.SUCCESS, "创建【"+ databaseName + "】数据库成功", null, channelId);
        }catch (Exception ex){
            ex.printStackTrace();
            merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.FAIL, "创建【"+ databaseName +"】数据库异常", ex.getMessage(), channelId);
            if(connection != null) {
                connection.close();
            }
            return null;
        }

        // 数据库创建成功 创建链接
        connection = connection.setDatabaseName(databaseName).restart();
        return connection;
    }

    // 创建链接 不链接数据库
    private SqlConnection createSqlConnectionNoDatabase(Integer databaseId, String domain, Integer port, String databaseName, String username, String password, Integer createUser, String createName, String channelId) {
        AdminMerchantsDatabaseSync merchantsDatabaseSync = merchantsDatabaseSyncService.createRecord(databaseId, "发布数据库【"+ databaseName +"】", "创建数据库服务器链接", createUser, createName, channelId);
        SqlConnection sqlConnection = null;
        try {
            sqlConnection = DatabaseConnection.create(false, false)
                    .setProperty(domain, port, username, password).connection();
            merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.SUCCESS, "创建【"+ databaseName + "】服务器链接成功", null, channelId);
        }catch (Exception ex) {
            ex.printStackTrace();
            merchantsDatabaseSyncService.updateStatus(merchantsDatabaseSync.getId(), DatabaseRunStatus.FAIL, "创建【"+ databaseName +"】服务器链接异常", ex.getMessage(), channelId);
            if(sqlConnection != null){
                //关闭链接
                sqlConnection.close();
            }
            return null;
        }
        return sqlConnection;
    }
}
